package com.xl.competition.modul_c.task1

import org.apache.spark.sql.SparkSession

/**
 * @author: xl
 * @createTime: 2023/11/18 21:22:27
 * @program: com.xl.competition
 * @description: ${description}
 */
object SimilarTop10 {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession
      .builder()
      .master("local[*]")
      .appName(this.getClass.getName)
      .enableHiveSupport()
      .config("hive.metastore.uris", "thrift://node2:9083")
      .config("spark.sql.parquet.writeLegacyFormat", "true")
      .getOrCreate()

    spark.sql(
      """
        |select user_id,
        |       count(fod.id) cnt
        |from dwd.fact_order_detail fod
        |         join fact_order_info foi on foi.id = fod.order_id
        |where fod.sku_id in
        |      (
        |          select distinct fod.sku_id
        |          from dwd.fact_order_detail fod
        |                   join dwd.fact_order_info foi
        |                        on fod.order_id = foi.id
        |          where user_id = 67)
        |and user_id != 67
        |group by user_id
        |order by cnt desc
        |limit 10
        |""".stripMargin).show()


    spark.stop()
  }
}
